package com.maycurobj.repository.oa;

import com.maycurobj.entity.oa.JieKuanEntity;
import com.maycurobj.entity.oa.YiBanEntity;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface WorkflowByJieKuanRepository extends JpaRepository<JieKuanEntity,Integer> {

    /**
     * 借款审批（会议借款、保证金借款等oa流程）
     * sql 获取 工号对应流程列表 待分页
     * @param employeeId
     * @return
     */
    @Query(nativeQuery = true,value = "select distinct * from ( select DISTINCT biaoti ||'-'|| LIUCHENGBH AS biaoti,LIUCHENGBH,to_date(shenqingrq,'yyyy-mm-dd') as shenqingrq,caozuor,jine,requestid from (SELECT DISTINCT ( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti, f47.liuchengbh,f47.shenqingrq,lastname AS caozuor, nvl(caigouze,0.00) AS jine,f47.REQUESTID FROM formtable_main_29 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT ( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl(shijizfy,0.00) AS jine,f47.REQUESTID FROM formtable_main_68 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND (iscomplete = 1 OR f47.liuchengzt = 1 or shijizfy!=0) AND nodeid not IN(7947,9347) AND workcode = :employeeId ) ) where biaoti like %:keyWord% order by requestid desc ")
    List<JieKuanEntity> getWFs(@Param(value = "employeeId") String employeeId, @Param(value="keyWord") String keyWord ,Pageable pageable);

    /**
     * 获取全部记录(借款流程)
     */
    @Query(nativeQuery = true,value = "select DISTINCT count(*) as counts from ( select DISTINCT biaoti ||'-'|| LIUCHENGBH AS biaoti,LIUCHENGBH,to_date(shenqingrq,'yyyy-mm-dd') as shenqingrq,caozuor,jine,requestid from (SELECT DISTINCT ( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti, f47.liuchengbh,f47.shenqingrq,lastname AS caozuor, nvl(caigouze,0.00) AS jine,f47.REQUESTID FROM formtable_main_29 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT ( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl(shijizfy,0.00) AS jine,f47.REQUESTID FROM formtable_main_68 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND (iscomplete = 1 OR f47.liuchengzt = 1 or shijizfy!=0) AND nodeid not IN(7947,9347) AND workcode = :employeeId ) ) where biaoti like %:keyWord% ")
    int getCounts(@Param(value = "employeeId") String employeeId,@Param(value="keyWord") String keyWord);

}
